IF OBJECT_ID('ssf.ptx_insert_task') IS NOT NULL
	DROP PROCEDURE ssf.ptx_insert_task
GO

CREATE PROCEDURE ssf.ptx_insert_task
	@task_group_id INT
	, @task_database_name NVARCHAR(128) = NULL
	, @task_procedure_name NVARCHAR(256)
	, @task_parameter_list NVARCHAR(MAX) = NULL
	, @priority INT = 1
	, @semaphore_name NVARCHAR(128) = NULL
	, @active_fl BIT = 1
	, @task_id INT = NULL OUTPUT
AS
/*
<object_header>

<object_summary>
	<purpose><![CDATA[

	]]></purpose>
</object_summary>

</object_header>
*/

SET NOCOUNT ON

-- ============
/*<code_comment><![CDATA[declare dynamic sql variables]]></code_comment>*/
DECLARE	@proc_error INT
			, @sql_error INT
			, @error_message NVARCHAR(2048)
			, @error_line INT
			, @row_count BIGINT
			, @nsql NVARCHAR(MAX)
			, @crlf CHAR(2) = CHAR(13) + CHAR(10)
			, @quote CHAR(1) = CHAR(39)
			, @tab CHAR(1) = CHAR(9)
-- ============

-- ============
/*<code_comment><![CDATA[check parameters]]></code_comment>*/
IF @task_group_id IS NULL
OR NULLIF(@task_procedure_name, N'') IS NULL
OR @priority IS NULL
OR @active_fl IS NULL
BEGIN
	RAISERROR('A required parameter is blank or null.', 16, 1)
	RETURN 1
END
-- ============

-- ============
/*<code_comment><![CDATA[insert task]]></code_comment>*/
SET @task_id = NULL

INSERT ssf.ptx_task (
	task_group_id
	, task_database_name
	, task_procedure_name
	, task_parameter_list
	, priority
	, semaphore_name
	, active_fl
)
VALUES (
	@task_group_id
	, NULLIF(@task_database_name, N'')
	, @task_procedure_name
	, NULLIF(@task_parameter_list, N'')
	, @priority
	, NULLIF(@semaphore_name, N'')
	, @active_fl
)

SELECT @sql_error = @@ERROR
	, @row_count = @@ROWCOUNT
	, @task_id = SCOPE_IDENTITY()

IF @sql_error <> 0
BEGIN
	RAISERROR('The INSERT into the table <ssf.ptx_task> failed.', 16, 1)
	RETURN @sql_error
END
-- ============

RETURN @@ERROR
GO
